const { DataTypes } = require('sequelize')

module.exports = (sequelize) => {
  const MemberUpgrade = sequelize.define('MemberUpgrade', {
    id: {
      type: DataTypes.INTEGER,
      primaryKey: true,
      autoIncrement: true
    },
    user_id: {
      type: DataTypes.BIGINT.UNSIGNED,
      allowNull: false,
      comment: '用户ID'
    },
    level_id: {
      type: DataTypes.INTEGER,
      allowNull: false,
      comment: '会员等级ID'
    },
    previous_level: {
      type: DataTypes.INTEGER,
      allowNull: false,
      comment: '升级前等级'
    },
    new_level: {
      type: DataTypes.INTEGER,
      allowNull: false,
      comment: '升级后等级'
    },
    upgrade_type: {
      type: DataTypes.STRING(20),
      allowNull: false,
      comment: '升级类型: points, purchase, admin'
    },
    points_used: {
      type: DataTypes.INTEGER,
      allowNull: true,
      comment: '使用的积分'
    },
    amount_paid: {
      type: DataTypes.DECIMAL(10, 2),
      allowNull: true,
      comment: '支付金额'
    },
    order_id: {
      type: DataTypes.BIGINT.UNSIGNED,
      allowNull: true,
      comment: '关联订单ID'
    },
    expiry_date: {
      type: DataTypes.DATE,
      allowNull: true,
      comment: '过期时间'
    },
    status: {
      type: DataTypes.TINYINT,
      allowNull: false,
      defaultValue: 1,
      comment: '状态: 0-已过期, 1-有效'
    },
    created_at: {
      type: DataTypes.DATE,
      allowNull: false,
      defaultValue: DataTypes.NOW,
      comment: '创建时间'
    },
    updated_at: {
      type: DataTypes.DATE,
      allowNull: false,
      defaultValue: DataTypes.NOW,
      comment: '更新时间'
    }
  }, {
    tableName: 'member_upgrades',
    timestamps: true,
    createdAt: 'created_at',
    updatedAt: 'updated_at',
    indexes: [
      {
        name: 'idx_user_id',
        fields: ['user_id']
      },
      {
        name: 'idx_level_id',
        fields: ['level_id']
      },
      {
        name: 'idx_order_id',
        fields: ['order_id']
      },
      {
        name: 'idx_created_at',
        fields: ['created_at']
      },
      {
        name: 'idx_expiry_date',
        fields: ['expiry_date']
      }
    ]
  })

  MemberUpgrade.associate = (models) => {
    MemberUpgrade.belongsTo(models.User, {
      foreignKey: 'user_id',
      as: 'user'
    })
    
    MemberUpgrade.belongsTo(models.MemberLevel, {
      foreignKey: 'level_id',
      as: 'level'
    })
    
    MemberUpgrade.belongsTo(models.Order, {
      foreignKey: 'order_id',
      as: 'order'
    })
  }

  return MemberUpgrade
}